package com.DAO;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.sql.*;

import com.beans.*;

import com.connection.ConnectionFactory;

public class UserDAO {

	private Connection con=null;
	public UserDAO()
	{
		
		con = ConnectionFactory.getConnection();
		
	}
	
	
	
	public ArrayList<UserForm> get()  throws  SQLException
	{	
		ArrayList<UserForm> list = new ArrayList<UserForm>();
		
		String query = "select *from users WHERE is_admin='F'";
		Statement st = null;
		ResultSet rs = null;
		try 
		{

			st = con.createStatement();
			rs = st.executeQuery(query);
			
			while(rs.next())
			{	
				UserForm uObj= new UserForm();
				uObj.setUserId(rs.getString(1));
				uObj.setPassword(rs.getString(2));
				uObj.setUserName(rs.getString(3));
				uObj.setBirthDate(rs.getString(4));
				uObj.setIsAdmin(rs.getString(5));
				
				list.add(uObj);
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (st != null) 
				{
					st.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
			return list;				//returnn title , description , creator_id , date & time 
	}

	
	public UserForm getUserInfo(String id,String password)
	{
		String query="select * from users where id=? and password=?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		UserForm uObj=new UserForm();
		
		try 
		{
			
			ps= con.prepareStatement(query);
			ps.setString(1,id);
			ps.setString(2,password);
			rs = ps.executeQuery();
			while(rs.next()) {
				
				uObj.setUserId(rs.getString(1));
				uObj.setPassword(rs.getString(2));
				uObj.setUserName(rs.getString(3));
				uObj.setBirthDate(rs.getString(4));
				uObj.setIsAdmin(rs.getString(5));
				
			}

		} catch (SQLException e)
		{
			System.out.println("Error in get user info....................");			
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
				
		}
		return uObj;
	}
	
	
	public String getUsernameById(String id)
	{
		String query="select name from users where id=?";
		PreparedStatement ps=null;
		ResultSet rs=null;
		String name = "";
		try 
		{
			
			ps= con.prepareStatement(query);
			ps.setString(1,id);
			
			rs = ps.executeQuery();
			rs.next();
			name = rs.getString(1);

		} catch (SQLException e)
		{
			System.out.println("Error in get user info....................");			
		} finally 
		{
			/* close connection */
			try 
			{
				
				if (ps != null) 
				{
					ps.close();
				}
				if (rs != null) 
				{
					rs.close();
				}

			} catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
				
		}
		return name;
	}
	
	
	public int insert(UserForm u) throws SQLException 
	{
		// TODO Auto-generated method stub
		
		
		String id=u.getUserId();
		System.out.println("id is.........."+id);
		String password=u.getPassword();
		String userName=u.getUserName();
		String birthDate=u.getBirthDate();
		String isAdmin = "F";
		
		
		String query = "INSERT into users values(?,?,?,?,?)";
		PreparedStatement ps = null;
		int rowsAffected=0;
		try 
		{
			ps=con.prepareStatement(query);
			ps.setString(1,id);
			ps.setString(2,password);
			ps.setString(3,userName);
			ps.setString(4,birthDate);
			ps.setString(5,isAdmin);
			rowsAffected = ps.executeUpdate();
			System.out.println("updated......");
			
					
		} catch (SQLException e)
			{
			    System.out.println("Error in prepare statement.......................");
				e.printStackTrace();
			} finally 
				{
					
					try 
					{
						if (ps != null) 
						{
							ps.close();
						}
			
					} catch (SQLException e) 
					{
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				
				}
		
			return rowsAffected;
	}

	public int delete(String id)  throws SQLException
	{
		// TODO Auto-generated method stub
		
		String query = "delete from users where id=?";
		
		PreparedStatement ps = null;
		int rowsAffected=0;
		
		try {	
				ps=con.prepareStatement(query);
				ps.setString(1,id);
				rowsAffected = ps.executeUpdate();
			} catch (SQLException e)
			{
				e.printStackTrace();
			} finally 
			{
				/* close connection */
				try 
				{
					
					if (ps != null) 
					{
						ps.close();
					}

				} catch (SQLException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			
			}
		return rowsAffected;
	}
	

	public void closeConnection()  throws SQLException
	{
		con.close();
		ConnectionFactory.closeConnection();
	}

}
